﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.OleDb;
using System.Web.UI.WebControls;
using System.Collections;
using System.IO;
using System.Web.UI;
using System.Text;

namespace MyLib
{
    public class ExcelUtil
    {
        const string connStr = "Provider={0};Data Source='{1}';Extended Properties='Excel {2};HER=YES;IMEX=1'";

        public ExcelUtil()
        {

        }

        public static void Export(object dataList)
        {
            Export(dataList, null, null, "" );
        }

        public static void Export(object dataList, string[] fields, string[] headTexts, string title)
        {
            string encoding = "gb2312";
            GridView gvw = new GridView();
            int ColCount, i;

            //如果筛选的字段和对应的列头名称个数相对的情况下只导出指定的字段
            if (fields!=null && fields.Length != 0 && fields.Length == headTexts.Length)
            {
                ColCount = fields.Length;
                gvw.AutoGenerateColumns = false;

                for (i = 0; i < ColCount; i++)
                {
                    BoundField bf = new BoundField();
                    bf.DataField = fields[i];
                    bf.HeaderText = headTexts[i];
                    gvw.Columns.Add(bf);
                }
            }
            else
            {
                gvw.AutoGenerateColumns = true;
            }

            gvw.Font.Name = "Verdana";
            gvw.BorderStyle = System.Web.UI.WebControls.BorderStyle.Solid;
            gvw.HeaderStyle.BackColor = System.Drawing.Color.LightCyan;
            gvw.HeaderStyle.ForeColor = System.Drawing.Color.Black;
            gvw.HeaderStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
            gvw.HeaderStyle.Wrap = false;
            gvw.HeaderStyle.Font.Bold = true;
            gvw.HeaderStyle.Font.Size = 10;
            gvw.RowStyle.Font.Size = 10;
            gvw.DataSource = dataList;
            gvw.DataBind();

            //报表内容
            StringBuilder sb = new StringBuilder();
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
            gvw.RenderControl(oHtmlTextWriter);
            byte[] bContent = System.Text.Encoding.GetEncoding(encoding).GetBytes(oStringWriter.ToString());
            sb.Append(System.Text.Encoding.GetEncoding(encoding).GetString(bContent));

            //输出
            string fileName;
            fileName = string.Format("{1}{0:yyyy-MM-dd_HH_mm}.xls", DateTime.Now, title);
            fileName = HttpUtility.UrlEncode(fileName);
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.Buffer = true;
            System.Web.HttpContext.Current.Response.Charset = encoding;
            System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding(encoding);
            System.Web.HttpContext.Current.Response.HeaderEncoding = System.Text.Encoding.GetEncoding(encoding);
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";    //设置输出文件类型为excel文件。 
            System.Web.HttpContext.Current.Response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=" + encoding + "\" >");
            System.Web.HttpContext.Current.Response.Write(sb.ToString());
            System.Web.HttpContext.Current.Response.End();
        }

        public static DataTable ExecuteDataTable(string filePath)
        {
            DataSet ds=ExecuteDataSet(filePath, String.Empty);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            return new DataTable();
        }

        public static DataSet ExecuteDataSet(string filePath)
        {
            return ExecuteDataSet(filePath, String.Empty);
        }

        public static DataSet ExecuteDataSet(string filePath, string sql)
        {
            string connection = "";
            if (filePath.IndexOf(".xlsx") > 0)
            {
                connection = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + filePath + ";Extended Properties='Excel 12.0;HER=YES;IMEX=1'";
            }
            else
            {
                connection = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath + ";Extended Properties='Excel 8.0;HER=YES;IMEX=1'";
            }

            OleDbConnection objConn = null;
            DataSet ds = new DataSet();
            try
            {
                objConn = new OleDbConnection(connection);
                objConn.Open();
                OleDbDataAdapter sqlada = new OleDbDataAdapter();
                string strSql = "";
                if (string.IsNullOrEmpty(sql))
                {
                    DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                    int x = schemaTable.Rows.Count;
                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        string tableName = schemaTable.Rows[i][2].ToString().Trim();
                        strSql += "Select * From   [" + tableName + "];";
                    }
                }
                else
                {
                    strSql = sql;
                }
                OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
                sqlada.SelectCommand = objCmd;
                sqlada.Fill(ds, strSql);
                objConn.Close();
            }
            catch (Exception ex)
            {
                LogUtil.Error(ex);
                throw ex;
            }
            finally
            {
                objConn.Close();
            }

            return ds;
        }
            
    }

}



